package test;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.io.FileUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.sf.json.JSONObject;
import test.vo.CodeConfig;
import test.vo.CodeConfig.Tempate;
import test.vo.Columns;
import test.vo.DbTable;

/**
 * 
 * @author xuejianxin
 *
 */
public class CodeGeneratorService {
	private static final Log log = LogFactory.getLog(CodeGeneratorService.class);
	private static final String CONFIG_DB = "/config/db/";// 数据库配置目录
	private static final String CONFIG_CODE = "/config/code.properties";// 全局代码配置
	private static final String CONFIG_TEMPLATE = "/config/template/";// 代码模板目录
	private static final String JDBC_DRIVER = "jdbc.driver.";// 数据库驱动
	private static final String JDBC_URL = "jdbc.url";// 数据库连接url
	private static final String JDBC_USERNAME = "jdbc.username";// 数据库用户
	private static final String JDBC_PASSWORD = "jdbc.password";// 数据库密码
	private static final String SQL_TABLES = "sql.tables.";// 查询全部表
	private static final String SQL_COLUMNS = "sql.columns.";// 查询表的列信息
	private static final String SQL_PAGE = "sql.page.";// 限制sql执行时只返回一条数据
	private static final String DB_NAME = "db.name";// 数据库类型
	private static final String DB_NAME_MYSQL = "mysql";// 数据库类型

	private String rootPath;// 配置文件根目录
	private Properties code;// 代码全局配置
	private Map<String, Properties> dbPropertiesCache = new HashMap<>();// 缓存数据库配置信息

	/**
	 * 获取代码属性
	 * 
	 * @param key 键
	 * @return
	 */
	private String getCodeProperties(String key) {
		try {
			if (null == code) {
				File config = new File(getRootPath() + CONFIG_CODE);
				code = new Properties();
				code.load(new FileReader(config));
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return code.getProperty(key);
	}

	/**
	 * 获取数据库配置信息
	 * 
	 * @param dbConfigName 数据库配置文件名称
	 * @param key          键
	 * @return
	 */
	private String getDbProperties(String dbConfigName, String key) {
		Properties db = dbPropertiesCache.get(dbConfigName);
		if (null == db) {
			db = new Properties();
			try {
				File config = new File(getRootPath() + CONFIG_DB + dbConfigName);
				db.load(new FileReader(config));
				dbPropertiesCache.put(dbConfigName, db);
				Class.forName(getCodeProperties(JDBC_DRIVER + db.getProperty(DB_NAME)));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return (String) db.get(key);
	}

	/**
	 * 根据目录和文件后缀 枚举文件信息
	 * 
	 * @param dir        目录
	 * @param fileSuffix 文件后缀
	 * @return 符合条件的文件信息
	 */
	private List<File> getFiles(String dir, String fileSuffix) {
		List<File> files = new ArrayList<File>();
		files.addAll(FileUtils.listFiles(new File(dir), new String[] { fileSuffix }, false));
		return files;
	}

	/**
	 * 获取程序根目录
	 * 
	 * @return
	 */
	private String getRootPath() {
		if (null == rootPath) {
			try {
				File d = new File(".");
				rootPath = d.getCanonicalPath();
				log("root path", rootPath);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return rootPath;
	}

	/**
	 * 返回所有的数据库配置文件
	 * 
	 * @return
	 */
	public List<File> getConfigDb() {
		return this.getFiles(getRootPath() + CONFIG_DB, "properties");
	}

	/**
	 * 返回所有的模板信息配置文件
	 * 
	 * @return
	 */
	public List<File> getConfigTemplates() {
		return this.getFiles(getRootPath() + CONFIG_TEMPLATE, "json");
	}

	public void log(String name, Object... args) {
		StringBuilder sb = new StringBuilder();
		sb.append(name);
		if (args.length > 0) {
			for (Object o : args) {
				sb.append("\t");
				sb.append(o);
			}
		}
		log.info(sb);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @param dbConfigName
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	private Connection getConn(String dbConfigName) throws SQLException {
		try {
			Connection conn = DriverManager.getConnection(getDbProperties(dbConfigName, JDBC_URL),
					getDbProperties(dbConfigName, JDBC_USERNAME), getDbProperties(dbConfigName, JDBC_PASSWORD));
			return conn;
		} catch (Exception e) {
			throw new RuntimeException(String.format("can not connect to db,[%s]\n",dbConfigName));
		}
	}

	/**
	 * 获取数据库表
	 * 
	 * @param dbConfigName 数据库配置文件
	 * @param tables       查询表名
	 * @return 符合条件的表信息
	 * @throws SQLException
	 */
	public List<String[]> getTableList(String dbConfigName, String... tables) throws Exception {
		List<String[]> data = new ArrayList<String[]>();
		String sql = getCodeProperties(SQL_TABLES + getDbProperties(dbConfigName, DB_NAME));
		System.out.println(sql);
		try (Connection conn = getConn(dbConfigName); PreparedStatement ps = conn.prepareStatement(sql)) {
			String pTable = "%";// 查询的表名
			if (null != tables && tables.length > 0) {
				pTable = "%" + tables[0] + "%";
			}
			ps.setString(1, pTable);

			ResultSet rs = ps.executeQuery();
			int i = 1;
			while (rs.next()) {
				data.add(new String[] { String.valueOf(i++), rs.getString(DbTable.name[1]),
						rs.getString(DbTable.name[2]), rs.getString(DbTable.name[3]) });
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		}
		return data;
	}

	/**
	 * 读取模板配置
	 * 
	 * @param configName 配置文件名称
	 * @return 解析后的配置文件信息
	 * @throws IOException
	 */
	public CodeConfig readConfig(String configName) throws IOException {
		JSONObject obj;
		CodeConfig config = null;
		Map<String, Class<Tempate>> classMap = new HashMap<String, Class<Tempate>>();
		classMap.put("templates", CodeConfig.Tempate.class);
		String path = getRootPath() + CONFIG_TEMPLATE + configName;
		StringBuilder sb = new StringBuilder();
		List<String> lines = FileUtils.readLines(new File(path), "utf-8");
		// 跳过空行和注释行
		lines.stream().filter(s -> !s.startsWith("#") && !"".equals(s.trim())).forEach(s -> sb.append(s));
		// 将json字符串转换为json对象
		obj = JSONObject.fromObject(sb.toString());
		config = (CodeConfig) JSONObject.toBean(obj, CodeConfig.class, classMap);// 将建json对象转换为对象
		config.setName(configName.substring(0, configName.indexOf(".json")));
		config.setTemplateDir(this.getRootPath() + CONFIG_TEMPLATE + config.getName());

		// 将json对象转换为java对象
		return config;
	}

	/**
	 * 获取表中的列信息
	 * 
	 * @param dbConfigName 数据库配置文件名称
	 * @param tableName    表名
	 * @return 列信息
	 * @throws Exception
	 */
	public List<Columns> getTableInfo(String dbConfigName, String tableName, String schemaName) throws Exception {
		String sql = getCodeProperties(SQL_COLUMNS + getDbProperties(dbConfigName, DB_NAME));
		List<Columns> list = new ArrayList<Columns>();
		try (Connection conn = getConn(dbConfigName); PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setString(1, tableName);
			ps.setString(2, schemaName);// 数据库
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String columnName = rs.getString(2);
				Columns c = new Columns(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4),
						null == rs.getString(5) ? "" : rs.getString(5), CodeGenerate.toBeanLabel(columnName));
				list.add(c);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		}
		return list;
	}

	/**
	 * 执行一个sql 返回sql的元信息，必须保证sql 有数据返回，否则无法获取列信息
	 * 
	 * @param dbConfigName 数据库配置文件名称
	 * @param tableName    虚拟表名
	 * @param sql          sql内容
	 * @return 返回sql的元信息，即sql的列信息
	 * @throws Exception
	 */
	public List<Columns> getSqlInfo(String dbConfigName, String tableName, String sql) throws Exception {
		String page = getCodeProperties(SQL_PAGE + getDbProperties(dbConfigName, DB_NAME));
		sql = String.format("select t_.* from ( %s ) t_  %s ", sql, page);
		List<Columns> list = new ArrayList<Columns>();
		try (Connection conn = getConn(dbConfigName);
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery()) {
			ResultSetMetaData rsm = rs.getMetaData(); // 获取结果的集的元数据
			if (!rs.next()) {
				throw new RuntimeException("请保证查询sql能够返回数据,否则无法获取字段类型信息!");
			}
			int colsCount = rsm.getColumnCount();
			for (int i = 1; i <= colsCount; i++) {
				String columnLabel = rsm.getColumnLabel(i);
				Columns c = new Columns(i, columnLabel, rsm.getPrecision(i), rsm.getColumnTypeName(i), columnLabel,
						CodeGenerate.toBeanLabel(columnLabel));
				list.add(c);
			}
		} catch (Exception e) {
			throw e;
		}
		return list;
	}

}
